﻿using Cheems.Interface.SQL;
using MySql.Data.MySqlClient;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Cheems.Service.SQL
{
    public class MySqlRepository<TEntity> : IBaseRepository<TEntity> where TEntity : class, new()
    {
        private string _connectionString;
        private string _tableName;
        private static List<TEntity> _cache = new List<TEntity>(); // 用来缓存实体的列表
        private Timer _timer;
        private static readonly object _lockObj = new object(); // 用来保护缓存的锁对象

        public MySqlRepository(string connectionString)
        {
            _connectionString = connectionString;
            _timer = new Timer(InsertDataFromCache, null, Timeout.Infinite, Timeout.Infinite); // 初始化定时器
        }
         
        private SqlSugarClient CreateDbContext()
        {
            try
            {
                // 构建初始连接字符串
                var initialConnectionString = _connectionString;
                var client = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = initialConnectionString, // 连接字符串
                    DbType = SqlSugar.DbType.MySql, // 数据库类型
                    IsAutoCloseConnection = true // 自动关闭连接
                });

                // 获取数据库名称
                var dbName = new MySqlConnectionStringBuilder(initialConnectionString).Database;

                // 构建没有数据库名称的连接字符串（用于检查数据库是否存在）
                var connectionStringWithoutDatabase = new MySqlConnectionStringBuilder(initialConnectionString)
                {
                    Database = string.Empty
                }.ToString();

                // 使用没有数据库名称的连接字符串创建客户端
                var tempClient = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = connectionStringWithoutDatabase, // 连接字符串没有指定数据库
                    DbType = SqlSugar.DbType.MySql, // 数据库类型
                    IsAutoCloseConnection = true // 自动关闭连接
                });

                // 检查数据库是否存在
                bool dbExists = tempClient.DbMaintenance.GetDataBaseList().Any(db => db.Equals(dbName, StringComparison.OrdinalIgnoreCase));
                if (!dbExists)
                {
                    // 创建数据库并定义字符集和排序规则
                    var createDatabaseSql = $"CREATE DATABASE `{dbName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;";
                    tempClient.Ado.ExecuteCommand(createDatabaseSql);
                }

                // 使用目标数据库更新连接字符串
                var updatedConnectionString = new MySqlConnectionStringBuilder(initialConnectionString)
                {
                    Database = dbName
                }.ToString();

                // 更新 SqlSugarClient 的连接字符串
                client.CurrentConnectionConfig.ConnectionString = updatedConnectionString;

                // 检查并创建表
                CheckAndCreateTable(client);
                return client;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 检查是否存在表格，如果没有就创建
        /// </summary>
        /// <param name="client"></param>
        private void CheckAndCreateTable(SqlSugarClient client)
        {
            if (string.IsNullOrEmpty(_tableName))
            {
                if (!client.DbMaintenance.IsAnyTable(typeof(TEntity).Name))
                {
                    client.CodeFirst.InitTables(typeof(TEntity));
                }
            }
            else
            {
                if (!client.DbMaintenance.IsAnyTable(_tableName))
                {
                    client.CodeFirst.As<TEntity>(_tableName).InitTables(typeof(TEntity)); // 初始化表
                }
            }

            if (string.IsNullOrEmpty(_tableName))
            {
                var tableAttribute = typeof(TEntity).GetCustomAttributes(typeof(SugarTable), true).FirstOrDefault() as SugarTable;
                string tableName = tableAttribute?.TableName ?? typeof(TEntity).Name; // 默认使用类名

                _tableName = tableName;
            }

        }

        public int CurrentDataSize
        {
            get
            {
                using (var db = CreateDbContext())
                {
                    return db.Queryable<TEntity>().Count();
                }
            }
        }

        public void UpdateConnectionString(string connectionString)
        {
            _connectionString = connectionString;
        }

        public async Task<int> Insert(TEntity entity, bool immediatelySave = false)
        {
            if (immediatelySave)
            {
                using (var db = CreateDbContext())
                {
                    return await db.Insertable(entity).ExecuteCommandAsync();
                }
            }
            else
            {
                // 将数据缓存到列表中
                lock (_lockObj) // 确保线程安全
                {
                    _cache.Add(entity);
                }

                _timer.Change(1000, Timeout.Infinite);
                return 1;
            }
        }


        private void InsertDataFromCache(object state)
        {
            lock (_cache)
            {
                if (_cache.Any())
                {
                    using (var db = CreateDbContext())
                    {
                        db.Insertable(_cache).ExecuteCommandAsync();
                    }
                    _cache.Clear(); // 清空缓存
                }
            }
        }

        public async Task<int> Insert(List<TEntity> entities)
        {
            using (var db = CreateDbContext())
            {
                return await db.Insertable(entities).ExecuteCommandAsync();
            }
        }

        public async Task<int> Delete(Expression<Func<TEntity, bool>> whereExpression)
        {
            using (var db = CreateDbContext())
            {
                return await db.Deleteable<TEntity>().Where(whereExpression).ExecuteCommandAsync();
            }
        }

        public async Task<int> Update(TEntity entity)
        {
            using (var db = CreateDbContext())
            {
                return await db.Updateable(entity).ExecuteCommandAsync();
            }
        }

        public async Task<int> UpdateColumns(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, TEntity>> updateColumns)
        {
            using (var db = CreateDbContext())
            {
                return await db.Updateable<TEntity>()
                                 .SetColumns(updateColumns)
                                 .Where(whereExpression)
                                 .ExecuteCommandAsync();
            }
        }

        public async Task<List<TEntity>> Query(Expression<Func<TEntity, bool>> whereExpression)
        {
            using (var db = CreateDbContext())
            {
                return await db.Queryable<TEntity>().Where(whereExpression).ToListAsync();
            }
        }

        public async Task<List<TEntity>> QueryAsync(Expression<Func<TEntity, bool>> whereExpression)
        {
            using (var db = CreateDbContext())
            {
                return await db.Queryable<TEntity>().Where(whereExpression).ToListAsync();
            }
        }

        public async Task<List<TEntity>> Query(Expression<Func<TEntity, bool>> whereExpression, int count)
        {
            using (var db = CreateDbContext())
            {
                return await db.Queryable<TEntity>().Where(whereExpression).Take(count).ToListAsync();
            }
        }

        public async Task<List<TEntity>> QueryAsync(Expression<Func<TEntity, bool>> whereExpression, int count)
        {
            using (var db = CreateDbContext())
            {
                return await db.Queryable<TEntity>().Where(whereExpression).Take(count).ToListAsync();
            }
        }

        public async Task<(List<TEntity>, int, int)> Query(Expression<Func<TEntity, bool>> whereExpression, int page, int size)
        {
            using (var db = CreateDbContext())
            {
                var query = db.Queryable<TEntity>().Where(whereExpression);
                var total = await query.CountAsync();
                var result = await query.Skip((page - 1) * size).Take(size).ToListAsync();
                return (result, total, page);
            }
        }

        public async Task<(List<TEntity>, int, int)> QueryAsync(Expression<Func<TEntity, bool>> whereExpression, int page, int size)
        {
            using (var db = CreateDbContext())
            {
                var query = db.Queryable<TEntity>().Where(whereExpression);
                var total = await query.CountAsync();
                var result = await query.Skip((page - 1) * size).Take(size).ToListAsync();
                return (result, total, page);
            }
        }

        public async Task<List<string>> GetTableStringList()
        {
            using (var db = CreateDbContext())
            {
                // 获取数据库表信息列表
                var tableInfoList = db.DbMaintenance.GetTableInfoList();

                // 提取表名并返回
                return tableInfoList.Select(t => t.Name).ToList();
            }
        }




        public async Task BackupDatabase(string backupFilePath)
        {
            await Task.Run(async () =>
            {
                using (var db = CreateDbContext())
                {
                    var databaseName = db.Ado.GetScalar("SELECT DATABASE()").ToString();

                    // 解析连接字符串以获取用户和密码
                    var userId = ""; // 从连接字符串中提取用户
                    var password = ""; // 从连接字符串中提取密码

                    var builder = new MySqlConnectionStringBuilder(_connectionString);
                    userId = builder.UserID;
                    password = builder.Password;

                    string strCmd = $"mysqldump -u {userId} -p{password} {databaseName} > {backupFilePath}";
                    RunCmd($"C:\\Program Files\\MySQL\\MySQL Server 9.0\\bin", strCmd);
                }
            });
        }

        public async Task RecoveryDatabase(string backupFilePath)
        {
            await Task.Run(async () =>
            {
                using (var db = CreateDbContext())
                {
                    var databaseName = db.Ado.GetScalar("SELECT DATABASE()").ToString();

                    // 解析连接字符串以获取用户和密码
                    var userId = ""; // 从连接字符串中提取用户
                    var password = ""; // 从连接字符串中提取密码

                    var builder = new MySqlConnectionStringBuilder(_connectionString);
                    userId = builder.UserID;
                    password = builder.Password;

                    string strCmd = $"mysql -u {userId} -p{password} {databaseName} < {backupFilePath}";
                    RunCmd($"C:\\Program Files\\MySQL\\MySQL Server 9.0\\bin", strCmd);

                }
            });
        }

        /// <summary>
        /// 通过环境变量查找MySql路径
        /// </summary>
        /// <returns></returns>
        private string GetMySqlBinPath()
        {
            // 尝试通过系统环境变量 PATH 查找 mysqldump 所在目录
            var pathVariable = Environment.GetEnvironmentVariable("PATH");
            if (string.IsNullOrEmpty(pathVariable))
            {
                return null;
            }

            // 遍历所有路径，查找 mysqldump.exe
            foreach (var path in pathVariable.Split(';'))
            {
                var mysqldumpPath = Path.Combine(path, "mysqldump.exe");
                if (File.Exists(mysqldumpPath))
                {
                    return path;  // 返回 mysqldump 所在的文件夹路径
                }
            }

            return null;
        }

        /// <summary>
        /// MySQL的mysqldump程序执行函数
        /// </summary>
        /// <param name="mysqldumPath">mysqldum工具路径</param>
        /// <param name="strCmd">cmd命令</param>
        /// <returns></returns>
        private string RunCmd(string mysqldumPath, string strCmd)
        {
            System.Diagnostics.Process p = new System.Diagnostics.Process();
            p.StartInfo.FileName = "cmd.exe";
            p.StartInfo.WorkingDirectory = mysqldumPath;
            p.StartInfo.UseShellExecute = false;
            p.StartInfo.RedirectStandardInput = true;
            p.StartInfo.RedirectStandardOutput = true;
            p.StartInfo.RedirectStandardError = true;
            p.StartInfo.CreateNoWindow = true;
            p.Start();
            p.StandardInput.WriteLine(strCmd);
            p.StandardInput.WriteLine("exit");
            return p.StandardError.ReadToEnd();
        }
    }
}
